﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using Tesseract.DataAccessBase.Attributes;

namespace Tesseract.DataAccessBase
{
    /// <summary>
    /// This is the base class for all data access classes.
    /// Connection string name can be specified using the class and method level attribute <see cref="ConnectionName"/>.
    /// Make sure the connection string in your config file is named "DBConnection".
    /// </summary>
    public abstract class DataAccessBase
    {
        private string connString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;

        /// <summary>
        /// For Executing Insert sprocs.
        /// </summary>
        /// <typeparam name="T">Type of entity being used.</typeparam>
        /// <param name="entity">The entity to insert.</param>
        /// <returns>Rows Inserted, unless the sproc specifies returning the Id</returns>
        protected int ExecInsert<T>(T entity)
        {
            MethodInfo method = (MethodInfo)new StackFrame(1).GetMethod();
            GetConnectionString(method);

            StoredProcedure sp = new StoredProcedure(entity, method, Command.Insert);

            return ExecuteNonQuery(sp);
        }

        /// <summary>
        /// For Executing Insert sprocs.
        /// </summary>
        /// <typeparam name="T">Type of entity being used.</typeparam>
        /// <param name="entity">The entity to insert.</param>
        /// <returns>List containing return data</returns>
        protected List<T> ExecInsertWithReturnData<T>(T entity)
        {
            MethodInfo method = (MethodInfo)new StackFrame(1).GetMethod();
            GetConnectionString(method);

            StoredProcedure sp = new StoredProcedure(entity, method, Command.Insert);

            return ExecuteNonQuery<T>(sp);
        }

        /// <summary>
        /// Executes Select stored procedures.
        /// </summary>
        /// <typeparam name="T">Type of class to populate</typeparam>
        /// <param name="p">The parameters.</param>
        /// <returns>List containing return data.</returns>
        protected List<T> ExecSelect<T>(params object[] p)
        {
            MethodInfo method = (MethodInfo)new StackFrame(1).GetMethod();
            GetConnectionString(method);

            StoredProcedure sp = new StoredProcedure(method, p);

            return Execute<T>(sp);
        }

        /// <summary>
        /// Executes Select stored procedures.
        /// </summary>
        /// <typeparam name="T">Type of class to populate</typeparam>
        /// <param name="entity">The entity to populate.</param>
        /// <param name="p">The parameters.</param>
        protected void ExecSelectOnExistingEntity<T>(T entity, params object[] p)
        {
            MethodInfo method = (MethodInfo)new StackFrame(1).GetMethod();
            GetConnectionString(method);

            StoredProcedure sp = new StoredProcedure(method, p);

            Execute(entity, sp);
        }

        /// <summary>
        /// For Executing Update sprocs.
        /// </summary>
        /// <typeparam name="T">Type of entity being used.</typeparam>
        /// <param name="entity">The entity.</param>
        /// <returns>Rows Updated</returns>
        protected int ExecUpdate<T>(T entity)
        {
            MethodInfo method = (MethodInfo)new StackFrame(1).GetMethod();
            GetConnectionString(method);

            StoredProcedure sp = new StoredProcedure(entity, method, Command.Update);

            return ExecuteNonQuery(sp);
        }

        /// <summary>
        /// For Executing Update sprocs.
        /// </summary>
        /// <typeparam name="T">Type of entity being used.</typeparam>
        /// <param name="entity">The entity.</param>
        /// <returns>List containing return data.</returns>
        protected List<T> ExecUpdateWIthReturnData<T>(T entity)
        {
            MethodInfo method = (MethodInfo)new StackFrame(1).GetMethod();
            GetConnectionString(method);

            StoredProcedure sp = new StoredProcedure(entity, method, Command.Update);

            return ExecuteNonQuery<T>(sp);
        }

        /// <summary>
        /// Execs the delete.
        /// </summary>
        /// <param name="p">The parameters.</param>
        /// <returns>Rows Deleted</returns>
        protected int ExecDelete(params object[] p)
        {
            MethodInfo method = (MethodInfo)new StackFrame(1).GetMethod();
            GetConnectionString(method);

            StoredProcedure sp = new StoredProcedure(method, p);

            return ExecuteNonQuery(sp);
        }

        /// <summary>
        /// Executes Scalar on stored procedure.
        /// </summary>
        /// <typeparam name="T">Type of return value</typeparam>
        /// <param name="p">The parameters.</param>
        /// <returns>Single data value.</returns>
        protected T ExecScalar<T>(params object[] p)
        {
            MethodInfo method = (MethodInfo)new StackFrame(1).GetMethod();
            GetConnectionString(method);

            StoredProcedure sp = new StoredProcedure(method, p);

            return ExecuteScalar<T>(sp);
        }

        #region Actual SQL connect and exec

        /// <summary>
        /// Executes the specified Stored Procedure.
        /// </summary>
        /// <typeparam name="T">The type being used.</typeparam>
        /// <param name="sp">The populated StoredProcedure object.</param>
        /// <returns>Collection containing populated objects of the type being used.</returns>
        private List<T> Execute<T>(StoredProcedure sp)
        {
            List<T> dataList = new List<T>();

            using (DbConnection conn = new SqlConnection(connString))
            {
                conn.Open();

                using (DbCommand cmd = new SqlCommand(sp.spName, (SqlConnection)conn))
                {
                    AddParamsToCommand((SqlCommand)cmd, sp.ParameterValueDictionary);

                    cmd.CommandType = CommandType.StoredProcedure;

                    // get data stream
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            // write each record to T
                            ReadDataIntoEntity(reader, dataList);
                        }
                    }
                }
            }

            return dataList;
        }

        /// <summary>
        /// Executes the specified Stored Procedure and populates the supplied entity.
        /// </summary>
        /// <typeparam name="T">The type being used.</typeparam>
        /// <param name="entity">The entity.</param>
        /// <param name="sp">The populated StoredProcedure object.</param>
        private void Execute<T>(T entity, StoredProcedure sp)
        {
            using (DbConnection conn = new SqlConnection(connString))
            {
                conn.Open();

                using (DbCommand cmd = new SqlCommand(sp.spName, (SqlConnection)conn))
                {
                    AddParamsToCommand((SqlCommand)cmd, sp.ParameterValueDictionary);

                    cmd.CommandType = CommandType.StoredProcedure;

                    // get data stream
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            // write each record to T
                            ReadDataIntoExistingEntity(entity, reader);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Executes a non query using the specified Stored Procedure.
        /// </summary>
        /// <param name="sp">The populated StoredProcedure object.</param>
        /// <returns>Stored Procedure return value</returns>
        private int ExecuteNonQuery(StoredProcedure sp)
        {
            int returnValue = 0;

            using (DbConnection conn = new SqlConnection(connString))
            {
                conn.Open();

                using (DbCommand cmd = new SqlCommand(sp.spName, (SqlConnection)conn))
                {
                    AddParamsToCommand((SqlCommand)cmd, sp.ParameterValueDictionary);

                    cmd.CommandType = CommandType.StoredProcedure;

                    // get data stream
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();

                            returnValue = Convert.ToInt32(reader.GetValue(0));
                        }
                    }
                }
            }

            return returnValue;
        }

        /// <summary>
        /// Executes a non query using the specified Stored Procedure.
        /// </summary>
        /// <param name="sp">The populated StoredProcedure object.</param>
        /// <returns>Stored Procedure return value</returns>
        private T ExecuteScalar<T>(StoredProcedure sp)
        {
            object returnValue = 0;

            using (DbConnection conn = new SqlConnection(connString))
            {
                conn.Open();

                using (DbCommand cmd = new SqlCommand(sp.spName, (SqlConnection)conn))
                {
                    AddParamsToCommand((SqlCommand)cmd, sp.ParameterValueDictionary);

                    cmd.CommandType = CommandType.StoredProcedure;

                    // get data stream
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();

                            returnValue = reader.GetValue(0);
                        }
                    }
                }
            }

            return (T)returnValue;
        }

        /// <summary>
        /// Executes a non query.
        /// </summary>
        /// <typeparam name="T">the type of entity being used.</typeparam>
        /// <param name="sp">The populated StoredProcedure object.</param>
        /// <returns>Collection containing populated objects of the type being used.</returns>
        private List<T> ExecuteNonQuery<T>(StoredProcedure sp)
        {
            List<T> dataList = new List<T>();

            using (DbConnection conn = new SqlConnection(connString))
            {
                conn.Open();

                using (DbCommand cmd = new SqlCommand(sp.spName, (SqlConnection)conn))
                {
                    AddParamsToCommand((SqlCommand)cmd, sp.ParameterValueDictionary);

                    cmd.CommandType = CommandType.StoredProcedure;

                    // get data stream
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            // write each record to T
                            ReadDataIntoEntity(reader, dataList);
                        }
                    }
                }
            }

            return dataList;
        }

        #endregion

        #region Helpers

        private void GetConnectionString(MemberInfo method)
        {
            ConnectionName methodConnName = (ConnectionName)Attribute.GetCustomAttribute(method, typeof(ConnectionName));

            if (methodConnName != null)
            {
                connString = methodConnName.Name;
            }
            else
            {
                ConnectionName classConnName = (ConnectionName)Attribute.GetCustomAttribute(method.ReflectedType, typeof(ConnectionName));

                if (classConnName != null)
                {
                    connString = classConnName.Name;
                }
            }
        }

        /// <summary>
        /// Adds the params to database command.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <param name="p">The p.</param>
        private void AddParamsToCommand(SqlCommand cmd, Dictionary<string, object> p)
        {
            foreach (KeyValuePair<string, object> param in p)
            {
                cmd.Parameters.Add(new SqlParameter
                    {
                        ParameterName = "@" + param.Key,
                        Value = param.Value
                    });
            }
        }

        /// <summary>
        /// Reads the data into existing entity.
        /// </summary>
        /// <typeparam name="T">The type of entity being used</typeparam>
        /// <param name="entity">The entity.</param>
        /// <param name="reader">The data reader.</param>
        private void ReadDataIntoExistingEntity<T>(T entity, IDataReader reader)
        {
            reader.Read();

            //iterate through columns and set properties of the entity with the matching column values
            PopulateEntity(reader, entity);
        }

        /// <summary>
        /// Reads the data into entity.
        /// </summary>
        /// <typeparam name="T">The type of entity being used</typeparam>
        /// <param name="reader">The data reader.</param>
        /// <param name="dataList">The data list.</param>
        private void ReadDataIntoEntity<T>(IDataReader reader, ICollection<T> dataList)
        {
            while (reader.Read())
            {
                T entity = Activator.CreateInstance<T>();

                //iterate through columns and set properties of the entity with the matching column values
                PopulateEntity(reader, entity);

                //entity populated. Add it to the collection.
                dataList.Add(entity);
            }
        }

        /// <summary>
        /// Populates the entity.
        /// </summary>
        /// <typeparam name="T">The type of entity being used</typeparam>
        /// <param name="reader">The data reader.</param>
        /// <param name="entity">The entity.</param>
        private void PopulateEntity<T>(IDataRecord reader, T entity)
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                Type entityType = entity.GetType();

                //Had to use the table schema to match column name to property
                PropertyInfo pi = entityType.GetProperty(reader.GetName(i), BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase);

                //If we cannot find a matching property then search all properties for a matching ColumnName attribute
                if (pi == null)
                {
                    PropertyInfo[] properties = entityType.GetProperties();

                    pi = (from p in properties
                          let attribute =
                              Attribute.GetCustomAttribute(p, typeof(ColumnName)) as
                              ColumnName
                          where attribute != null
                                &&
                                attribute.Name.ToUpper().Equals(reader.GetName(i).ToUpper())
                          select p).SingleOrDefault();

                    //If the column name still does not match then skip it
                    if (pi == null)
                    {
                        //column cannot be matched to entity property, skip to next column.
                        continue;
                    }
                }

                Type currentPropertyType = pi.PropertyType;

                //Set the property value! Just make sure we change the type first....
                var val = reader.IsDBNull(i) ? GetDefault(pi.PropertyType) : ChangeType(reader[i], currentPropertyType);

                pi.SetValue(entity, val, null);
            }
        }

        private object GetDefault(Type type)
        {
            return type.IsValueType ? Activator.CreateInstance(type) : null;
        }

        /// <summary>
        /// Returns an Object with the specified Type and whose value is equivalent to the specified object.
        /// </summary>
        /// <param name="value">An Object that implements the IConvertible interface.</param>
        /// <param name="conversionType">The Type to which value is to be converted.</param>
        /// <returns>An object whose Type is conversionType (or conversionType's underlying type if conversionType
        /// is Nullable&lt;&gt;) and whose value is equivalent to value. -or- a null reference, if value is a null
        /// reference and conversionType is not a value type.</returns>
        /// <remarks>
        /// This method exists as a workaround to System.Convert.ChangeType(Object, Type) which does not handle
        /// nullables as of version 2.0 (2.0.50727.42) of the .NET Framework. The idea is that this method will
        /// be deleted once Convert.ChangeType is updated in a future version of the .NET Framework to handle
        /// nullable types, so we want this to behave as closely to Convert.ChangeType as possible.
        /// This method was written by Peter Johnson at:
        /// http://aspalliance.com/author.aspx?uId=1026.
        /// </remarks>
        public static object ChangeType(object value, Type conversionType)
        {
            // Note: This if block was taken from Convert.ChangeType as is, and is needed here since we're
            // checking properties on conversionType below.
            if (conversionType == null)
            {
                throw new ArgumentNullException("conversionType");
            } // end if

            // If it's not a nullable type, just pass through the parameters to Convert.ChangeType

            if (conversionType.IsGenericType &&
              conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            {
                // It's a nullable type, so instead of calling Convert.ChangeType directly which would throw a
                // InvalidCastException (per http://weblogs.asp.net/pjohnson/archive/2006/02/07/437631.aspx),
                // determine what the underlying type is
                // If it's null, it won't convert to the underlying type, but that's fine since nulls don't really
                // have a type--so just return null
                // Note: We only do this check if we're converting to a nullable type, since doing it outside
                // would diverge from Convert.ChangeType's behavior, which throws an InvalidCastException if
                // value is null and conversionType is a value type.
                if (value == null)
                {
                    return null;
                } // end if

                // It's a nullable type, and not null, so that means it can be converted to its underlying type,
                // so overwrite the passed-in conversion type with this underlying type
                NullableConverter nullableConverter = new NullableConverter(conversionType);
                conversionType = nullableConverter.UnderlyingType;
            } // end if

            // Now that we've guaranteed conversionType is something Convert.ChangeType can handle (i.e. not a
            // nullable type), pass the call on to Convert.ChangeType
            return Convert.ChangeType(value, conversionType);
        }

        #endregion
    }
}
